"""
Case Type   : 基础功能
Case Name   : 不同数据库创建同名发布
Create At   : 2022/1/27
Owner       : opentestcase026
Description :
    1.生成加密因子文件和密文文件
    2.在对应路径下删除生成的文件
    3.再次生成加密因子文件和密文文件
    4.两个数据库节点创建同名同字段表
    5.集群a，创建发布
    6.集群b，创建订阅，再次生成加密因子文件
    7.修改表数据，集群A：
    8.查询是否同步，集群B：
Expect      :
    1：成功
    2：删除成功
    3：成功
    4：创建表成功
    5：创建发布成功
    6：创建订阅成功
    7：插入数据成功
    8：数据同步成功
History     :
    Modified by opentestcase012 2022/10/11:优化用例和断言,创建订阅端时加参数 with (copy_data=false)
    Modified by opentestcase024 2023/8/23:增加订阅端同步等待,确保订阅端同步
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

Primary_SH = CommonSH('PrimaryDbUser')


@unittest.skipIf(3 != Primary_SH.get_node_num(), '非1+2环境不执行')
class Pubsubclass(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.log.info("-----------this is setup-----------")
        self.pri_userdb_pub = Node(node='PrimaryDbUser')
        self.pri_userdb_sub = Node(node='remote1_PrimaryDbUser')
        self.constant = Constant()
        self.commsh_pub = CommonSH('PrimaryDbUser')
        self.commsh_sub = CommonSH('remote1_PrimaryDbUser')
        self.com_pub = Common()
        self.tb_name1 = 'tb_pubsub_case146_1'
        self.subname1 = "sub_case146_1"
        self.pubname1 = "pub_case146_1"
        self.parent_path_sub = os.path.dirname(macro.DB_INSTANCE_PATH_REMOTE1)
        self.hba_pub = os.path.join(macro.DB_INSTANCE_PATH, 'pg_hba.conf')
        self.hba_sub = os.path.join(macro.DB_INSTANCE_PATH_REMOTE1,
                                    'pg_hba.conf')
        self.hba_bak_pub = os.path.join(
            os.path.dirname(macro.DB_INSTANCE_PATH), 'pg_hba.conf')
        self.hba_bak_sub = os.path.join(
            os.path.dirname(macro.DB_INSTANCE_PATH_REMOTE1), 'pg_hba.conf')
        self.port = str(int(self.pri_userdb_pub.db_port) + 1)
        self.wal_level = self.com_pub.show_param("wal_level")
        self.user_param_pub = f'-U {self.pri_userdb_pub.db_user} ' \
            f'-W {self.pri_userdb_pub.db_password}'
        self.user_param_sub = f'-U {self.pri_userdb_sub.db_user} ' \
            f'-W {self.pri_userdb_sub.db_password}'
        self.key_path_cipher = os.path.join(
            self.parent_path_sub, 'app', 'bin', 'subscription.key.cipher')
        self.key_path_rand = os.path.join(
            self.parent_path_sub, 'app', 'bin', 'subscription.key.rand')

        text = '-----预置条件1:备份配置文件pg_hba.conf expect:状态正常-----'
        self.log.info(text)
        cmd = f'cp {self.hba_pub} {self.hba_bak_pub};'
        self.log.info(cmd)
        msg1 = self.com_pub.get_sh_result(self.pri_userdb_pub, cmd)
        cmd = f'cp {self.hba_sub} {self.hba_bak_sub};'
        self.log.info(cmd)
        msg2 = self.com_pub.get_sh_result(self.pri_userdb_sub, cmd)
        self.assertEqual('', msg1 and msg2, '执行失败:' + text)

        text = '-----预置条件2:查看发布端和订阅端的数据库状态 expect:状态正常-----'
        self.log.info(text)
        status = self.commsh_pub.get_db_cluster_status()
        self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status,
                      '执行失败:' + text)
        status = self.commsh_sub.get_db_cluster_status(
            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status,
                      '执行失败:' + text)

        text = '-----预置条件3:修改发布端pg_hba文件和配置参数wal_level,重启数据库 expect:成功-----'
        self.log.info(text)
        guc_res = self.commsh_pub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, '',
            f'host    replication  {self.pri_userdb_sub.db_user} '
            f'{self.pri_userdb_sub.db_host}/32 sha256')
        self.log.info(guc_res)
        self.assertTrue(guc_res, '执行失败:' + text)
        guc_res = self.commsh_pub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, '',
            f'host    all  {self.pri_userdb_pub.db_user} '
            f'{self.pri_userdb_pub.db_host}/32 sha256')
        self.log.info(guc_res)
        self.assertTrue(guc_res, '执行失败:' + text)
        if 'logical' != self.wal_level:
            result = self.commsh_pub.execute_gsguc(
                'set', self.constant.GSGUC_SUCCESS_MSG, 'wal_level=logical')
            self.log.info(result)
            self.assertTrue(result, '执行失败:' + text)
            result = self.commsh_pub.restart_db_cluster(False)
            self.assertTrue(result, '执行失败:' + text)
            status = self.commsh_pub.get_db_cluster_status()
            self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status,
                          '执行失败:' + text)

        text = '-----预置条件4:订阅端修改pg_hba文件 expect:成功-----'
        self.log.info(text)
        guc_res = self.commsh_sub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host    replication  {self.pri_userdb_pub.db_user} '
            f'{self.pri_userdb_pub.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, '执行失败:' + text)
        guc_res = self.commsh_sub.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host all  {self.pri_userdb_sub.db_user} '
            f'{self.pri_userdb_sub.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, '执行失败:' + text)

    def test_pubsub(self):
        text = '--step1:生成加密因子文件和密文文件 expect:成功--'
        self.log.info(text)
        result = self.commsh_sub.execute_generate(
            macro.COMMON_PASSWD, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.assertIn('', result, '执行失败:' + text)

        text = '--step2:在对应路径下删除生成的文件 expect:成功--'
        self.log.info(text)
        cmd = f" rm -rf {self.key_path_cipher};" \
            f"rm -rf {self.key_path_rand}"
        result = self.pri_userdb_sub.sh(cmd).result()
        self.log.info(result)
        self.assertEqual('', result, '执行失败:' + text)

        text = '--step3:再次生成加密因子文件和密文文件 expect:成功--'
        self.log.info(text)
        result = self.commsh_sub.execute_generate(
            macro.COMMON_PASSWD, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.assertIn('', result, '执行失败:' + text)

        text = '--step4:两个数据库节点创建同名同字段表 expect:成功--'
        self.log.info(text)
        sql_table = f"drop table if exists {self.tb_name1};" \
            f"create table {self.tb_name1}(i int primary key, " \
            f"t boolean) with (segment=on);"
        result = self.commsh_pub.execut_db_sql(
            sql_table, self.user_param_pub)
        self.log.info(result)
        self.assertEqual(result.count(self.constant.TABLE_CREATE_SUCCESS),
                         2, '执行失败:' + text)
        result = self.commsh_sub.execut_db_sql(sql_table,
                                               self.user_param_sub, None,
                                               macro.DB_ENV_PATH_REMOTE1)
        self.log.info(result)
        self.assertEqual(result.count(self.constant.TABLE_CREATE_SUCCESS),
                         2, '执行失败:' + text)

        text = '--step5:集群a，创建发布 expect:成功--'
        self.log.info(text)
        sql = f"drop publication if exists {self.pubname1};" \
            f"create publication {self.pubname1} for all tables;"
        result = self.commsh_pub.execut_db_sql(sql,
                                               sql_type=self.user_param_pub)
        self.log.info(result)
        self.assertIn(self.constant.create_pub_succ_msg, result,
                      '执行失败:' + text)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], result,
                         '执行失败:' + text)

        text = '--step6:集群b，创建订阅 expect:成功--'
        self.log.info(text)
        sql = f"drop subscription if exists {self.subname1};" \
            f"create subscription {self.subname1} connection " \
            f"'host={self.pri_userdb_pub.db_host} " \
            f"port={self.port} " \
            f"user={self.pri_userdb_pub.db_user} " \
            f"dbname={self.pri_userdb_pub.db_name} " \
            f"password={self.pri_userdb_pub.ssh_password}' " \
            f"publication {self.pubname1} with (copy_data=false);"
        result = self.commsh_sub.execut_db_sql(sql, self.user_param_sub, None,
                                               macro.DB_ENV_PATH_REMOTE1)
        self.log.info(result)
        self.assertEqual(result.count(self.constant.create_sub_succ_msg), 1,
                         '执行失败:' + text)

        text = '--step7:7.修改表数据，集群A： expect:成功--'
        self.log.info(text)
        sql = f"insert into {self.tb_name1} values(1,True);"
        result = self.commsh_pub.execut_db_sql(sql,
                                               sql_type=self.user_param_pub)
        self.log.info(result)
        self.assertEqual(result.count(self.constant.INSERT_SUCCESS_MSG),
                         1, '执行失败' + text)

        self.log.info("----等待订阅端数据同步，超时时间为20s----")
        self.commsh_pub.check_sub_consistency(self.subname1,
                                              self.user_param_pub, 20)

        text = "--step8:查询是否同步  expect:数据同步成功--"
        self.log.info(text)
        sql_select = f"select * from {self.tb_name1};"
        result = self.commsh_sub.execut_db_sql(sql_select,
                                               self.user_param_sub,
                                               None,
                                               macro.DB_ENV_PATH_REMOTE1)
        self.log.info(result)
        self.assertIn('1 | t', result, '执行失败' + text)
        self.assertIn('1 row', result, '执行失败' + text)

    def tearDown(self):
        self.log.info('-----清理环境-----')
        text1 = '-----订阅端和发布端分别删除订阅和表 expect:成功-----'
        self.log.info(text1)
        sql = f"drop subscription if exists {self.subname1};" \
            f"drop table if exists {self.tb_name1}"
        drop_sub_result = self.commsh_sub.execut_db_sql(
            sql, self.user_param_sub, None, macro.DB_ENV_PATH_REMOTE1)
        self.log.info(drop_sub_result)
        sql = f"drop publication if exists {self.pubname1};" \
            f"drop table if exists {self.tb_name1}"
        drop_pub_result = self.commsh_pub.execut_db_sql(
            sql, sql_type=self.user_param_pub)
        self.log.info(drop_pub_result)

        text2 = '-----恢复pg_hba.conf文件 expect:成功-----'
        self.log.info(text2)
        cmd = f'if [[ -e {self.hba_pub} && -e {self.hba_bak_pub} ]]; ' \
            f'then rm -rf {self.hba_pub}; ' \
            f'mv {self.hba_bak_pub} {self.hba_pub}; ' \
            f'else echo not exits; fi'
        hba_msg_pub = self.com_pub.get_sh_result(self.pri_userdb_pub, cmd)
        cmd = f'if [[ -e {self.hba_sub} && -e {self.hba_bak_sub} ]]; ' \
            f'then rm -rf {self.hba_sub}; ' \
            f'mv {self.hba_bak_sub} {self.hba_sub}; ' \
            f'else echo not exits; fi'
        hba_msg_sub = self.com_pub.get_sh_result(self.pri_userdb_sub, cmd)

        text3 = '-----恢复发布端的wal_level参数值 expect:成功-----'
        self.log.info(text3)
        guc_res = self.commsh_pub.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f'wal_level={self.wal_level}')

        text4 = '-----重启数据库查看数据库状态 expect:成功-----'
        self.log.info(text4)
        res_msg_pub = self.commsh_pub.restart_db_cluster(False)
        status_pub = self.commsh_pub.get_db_cluster_status()
        self.log.info(status_pub)
        res_msg_sub = self.commsh_sub.restart_db_cluster(
            env_path=macro.DB_ENV_PATH_REMOTE1)
        status_sub = self.commsh_sub.get_db_cluster_status(
            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(status_sub)

        self.assertIn(self.constant.drop_sub_succ_msg, drop_sub_result,
                      '执行失败' + text1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_sub_result,
                      '执行失败' + text1)
        self.assertIn(self.constant.drop_pub_succ_msg, drop_pub_result,
                      '执行失败' + text1)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, drop_pub_result,
                      '执行失败' + text1)
        self.assertEqual('', hba_msg_pub and hba_msg_sub, '执行失败:' + text2)
        self.assertTrue(guc_res, '执行失败:' + text3)
        self.assertTrue(res_msg_pub, '执行失败:' + text4)
        self.assertTrue(res_msg_sub, '执行失败:' + text4)
        self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status_pub,
                      '执行失败:' + text4)
        self.assertIn(self.constant.CLUSTER_NORMAL_MSG, status_sub,
                      '执行失败:' + text4)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
